package com.cassiopae.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.cassiopae.entity.Profile;

public class ProfileDaoImpl extends IDumpDAO<Profile>{

	private Statement st;

	public Boolean create(Profile profile) {
		Connection cn = ConnectionDAO.openConnection();
		String requete = "INSERT INTO profile ("
				+ "`nom_profile`,`base_donnee`,`schema_cible`,`password_cible`,`version_db`,`version_appli`,`imp_schema`"
				+ ") VALUES ("
				+ "'"+profile.getNomProfile()+"','"+profile.getDatabaseCible()+"','"+profile.getSchemaCible()+"','"+profile.getPassword()+"','"+profile.getDbVersion()+"','"+profile.getAppliVersion()+"','"+profile.getImpSchema()+"')";

		Boolean rep = false;
		if (cn != null){

			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
				rep= true;
			} catch (SQLException e) {
				rep=false;
			}
		}
		return rep;
	}

	public Profile retrieveProfile(int id){
		Connection cn = ConnectionDAO.openConnection();
		ResultSet resultSet = null;
		Profile profile = null;
		String requete = "SELECT base_donnee, schema_cible, password_cible, status, version_appli, version_db, imp_schema FROM PROFILE WHERE id = '"+id+"'";
		if (cn != null){
			try {
				st = cn.createStatement();
				resultSet = st.executeQuery(requete);
				if(resultSet.next()){
					profile = new Profile();
					profile.setId(id);
					profile.setDatabaseCible(resultSet.getString( "BASE_DONNEE" ));
					profile.setSchemaCible(resultSet.getString( "SCHEMA_CIBLE" ));
					profile.setPassword(resultSet.getString("PASSWORD_CIBLE"));
					profile.setStatus(resultSet.getBoolean("STATUS"));
					profile.setAppliVersion(resultSet.getString("VERSION_APPLI"));
					profile.setDbVersion(resultSet.getString("VERSION_DB"));
					profile.setImpSchema(resultSet.getString("IMP_SCHEMA"));
				}
				cn.close();
				st.close();
			} catch (SQLException e) {
				profile = null;
			}
		}
		return profile;

	}


	public List<Profile> getAll() {

		List<Profile> profiles = new ArrayList<Profile>();
		Connection cn = ConnectionDAO.openConnection();
		ResultSet resultSet = null;
		Profile profile = null;
		String requete = "SELECT id, base_donnee, schema_cible, password_cible, version_db, version_appli, begin_time, end_time, status, imp_schema FROM PROFILE" ;
		if (cn != null){
			try {
				st = cn.createStatement();
				resultSet = st.executeQuery(requete);
				while (resultSet.next()){
					String database = resultSet.getString("BASE_DONNEE");
					String schemaCible = resultSet.getString("SCHEMA_CIBLE");
					String password = resultSet.getString("PASSWORD_CIBLE");
					String dbVersion = resultSet.getString("VERSION_DB");
					String appliVersion = resultSet.getString("VERSION_APPLI");
					Timestamp timestamp = resultSet.getTimestamp("BEGIN_TIME");
					Date beginDate = timestamp != null ?new Date( timestamp.getTime() ) : null;
					timestamp = resultSet.getTimestamp("END_TIME");
					Date endDate = timestamp != null ?new Date( timestamp.getTime() ) : null;
					String impSchema = resultSet.getString("IMP_SCHEMA");
					profile = new Profile(database, schemaCible, password, dbVersion, appliVersion, beginDate, endDate, impSchema);
					profile.setId(resultSet.getInt("ID"));
					profile.setStatus(resultSet.getBoolean("STATUS"));
					profiles.add(profile);
				}
				cn.close();
				st.close();
			} catch (SQLException e) {
				profile = null;
			}

		}
		return profiles;
	}


	public Boolean delete(int id) {
		Connection cn = ConnectionDAO.openConnection();
		String requete = "DELETE FROM PROFILE WHERE ID='"+id+"'";
		Boolean rep = false;
		if (cn != null){

			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
				rep= true;
			} catch (SQLException e) {
				rep=false;
			}
		}
		return rep;
	}

	public List<Profile> getProfilesBySchema(String schema) {

		List<Profile> profiles = new ArrayList<Profile>();
		Connection cn = ConnectionDAO.openConnection();
		ResultSet resultSet = null;
		Profile profile = null;
		String requete = "SELECT id, base_donnee, schema_cible, password_cible, version_db, version_appli, begin_time, end_time, status, imp_schema FROM PROFILE WHERE schema_cible = '"+schema+"'" ;
		if (cn != null){
			try {
				st = cn.createStatement();
				resultSet = st.executeQuery(requete);
				while (resultSet.next()){
					String database = resultSet.getString("BASE_DONNEE");
					String schemaCible = resultSet.getString("SCHEMA_CIBLE");
					String password = resultSet.getString("PASSWORD_CIBLE");
					String dbVersion = resultSet.getString("VERSION_DB");
					String appliVersion = resultSet.getString("VERSION_APPLI");
					Timestamp timestamp = resultSet.getTimestamp("BEGIN_TIME");
					Date beginDate = timestamp != null ?new Date( timestamp.getTime() ) : null;
					timestamp = resultSet.getTimestamp("END_TIME");
					Date endDate = timestamp != null ?new Date( timestamp.getTime() ) : null;
					String impSchema = resultSet.getString("IMP_SCHEMA");
					profile = new Profile(database, schemaCible, password, dbVersion, appliVersion, beginDate, endDate, impSchema);
					profile.setId(resultSet.getInt("ID"));
					profile.setStatus(resultSet.getBoolean("STATUS"));
					profiles.add(profile);
				}
				cn.close();
				st.close();
			} catch (SQLException e) {
				profile = null;
			}

		}
		return profiles;
	}
	
	public boolean deleteProfilesForUser(String email){
		Connection cn = ConnectionDAO.openConnection();
		String requete = "DELETE FROM USER_SCHEMA WHERE ID_USER='"+email+"'";
		Boolean rep = false;
		if (cn != null){

			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
				rep= true;
			} catch (SQLException e) {
				rep=false;
			}
		}
		return rep;
	}

	@Override
	public void update(Profile profile) {
	}
	
	public void updateStatusAndEndTime(Profile profile) {
		Connection cn = ConnectionDAO.openConnection();
		Character statusInt = profile.getStatus() ? '1' : '0';
		String requete = "UPDATE PROFILE SET "
				+ "STATUS='"+statusInt+"', "
				+ "END_TIME=NOW()"
				+ "WHERE ID='"+profile.getId()+"' ";
		if (cn != null){
			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
			} catch (SQLException e) {
			}
		}
	}
	
	public void updateStatusAndBeginTime(Profile profile) {
		Connection cn = ConnectionDAO.openConnection();
		Character statusInt = profile.getStatus() ? '1' : '0';
		String requete = "UPDATE PROFILE SET "
				+ "STATUS='"+statusInt+"', "
				+ "BEGIN_TIME = NOW(),"
				+ "END_TIME = '0000-00-00 00:00:00'"
				+ "WHERE ID='"+profile.getId()+"' ";
		if (cn != null){
			try {
				st = cn.createStatement();
				st.executeUpdate(requete);
				cn.close();
				st = null;
			} catch (SQLException e) {
			}
		}
	}

	@Override
	public Profile getById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Boolean delete(Profile profile) {
		// TODO Auto-generated method stub
		return false;
	}

}
